package vn.com.bookstore.daoImpl;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import vn.com.bookstore.dao.TransactionDao;
import vn.com.bookstore.domain.model.PagingInfo;
import vn.com.bookstore.domain.model.SearchResult;
import vn.com.bookstore.model.vo.TransactionVO;
import vn.com.bookstore.utils.DaoHelper;

@Repository
public class TransactionDaoImpl extends DaoHelper<TransactionVO> implements TransactionDao{
	
	@SuppressWarnings("rawtypes")
	class TransactionVoRowMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			TransactionVO transactionVO = new TransactionVO();
			transactionVO.setId(rs.getInt("id"));
			transactionVO.setBookId(rs.getInt("BookId"));
			transactionVO.setAudioBookId(rs.getInt("audioId"));
			transactionVO.setCustomerId(rs.getInt("CustomerId"));
			transactionVO.setTotalPrice(rs.getDouble("TotalPrice"));
			transactionVO.setTransactionDate(rs.getTimestamp("TransactionDate"));
			transactionVO.setBookName(rs.getString("BookName"));
			transactionVO.setPublisherId(rs.getInt("PublisherId"));
			transactionVO.setPublisherName(rs.getString("PublisherName"));
			transactionVO.setAuthor(rs.getString("Author"));
			return transactionVO;
		}
	}
	
	
	@Override
	public void saveTransaction(TransactionVO transactionVO) {
		String sql= "Insert Into transaction (id,customer_id,book_id,transaction_date,total_price, transaction_type, audio_book_id) Value (?,?,?,?,?, ?, ?)";
		Object[] params = new Object[] {transactionVO.getId(),transactionVO.getCustomerId(),transactionVO.getBookId(),transactionVO.getTransactionDate(),transactionVO.getTotalPrice(), transactionVO.getTransaction_type(), transactionVO.getAudioBookId()};
		insert(sql, params);
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public SearchResult<TransactionVO> getTransactionByCustomerId(PagingInfo pagingInfo, int customerId) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM (")
		.append("SELECT distinct (t.id) id, t.book_id BookId, t.audio_book_id audioId, t.transaction_date TransactionDate, t.total_price TotalPrice, b.name BookName,b.author Author, p.id PublisherId, p.name PublisherName,  t.customer_id CustomerId ")
		.append(" FROM transaction t left join book b on t.book_id = b.id ")
		.append(" inner join publisher p on b.publisher_id = p.id ")
		.append(" inner join customer c on t.customer_id = c.id ")
		.append(" union all ")
		.append(" SELECT distinct (t.id) id, t.book_id BookId, t.audio_book_id audioId, t.transaction_date TransactionDate, t.total_price TotalPrice, ab.title BookName, null Author, p.id PublisherId, p.name PublisherName,  t.customer_id CustomerId")
		.append(" FROM transaction t left join audio_book ab on t.audio_book_id = ab.id ")
		.append(" inner join publisher p on ab.publisher_id = p.id ")
		.append(" inner join customer c on t.customer_id = c.id ")
		.append(" ) AS A WHERE A.CustomerId = ?");
		Object[] params = new Object[]{customerId};
		SearchResult<TransactionVO> searchResult = queryPaging(sql.toString(), params, new TransactionVoRowMapper(), pagingInfo);
		return searchResult;
	}
}
